"""
用户表原子操作（异步版）
"""
from datetime import datetime
from typing import Optional, List, Tuple

from app.common.security import hash_password
from app.models.system_models import Role
from app.schemas.system_schemas import UserListRequest, UserWithRolesResponse, RoleResponse
from app.schemas.user_schemas import UserInDB, UserCreate
from common.database import DatabaseService
from common.exceptions import BizException, ErrorCode


# ---------- 基础查询 ----------
async def get_user_by_field(db: DatabaseService, field: str, value) -> Optional[UserInDB]:
	sql = f"""
        SELECT id, user_code, username, name, email, phone, hashed_password, disabled,
               created_at, updated_at, last_login
        FROM base_user
        WHERE {field} = %s AND deleted = 0
    """
	row = db.get_one(sql, (value,))
	return UserInDB.model_validate(row) if row else None


async def get_user_by_id(db: DatabaseService, user_id: int) -> Optional[UserInDB]:
	return await get_user_by_field(db, "id", user_id)


async def get_user_by_code(db: DatabaseService, user_code: str) -> Optional[UserInDB]:
	return await get_user_by_field(db, "user_code", user_code)


async def username_exists(db: DatabaseService, username: str) -> bool:
	return bool(db.get_one("SELECT 1 FROM base_user WHERE username = %s AND deleted = 0", (username,)))


async def email_exists(db: DatabaseService, email: str) -> bool:
	return bool(db.get_one("SELECT 1 FROM base_user WHERE email = %s AND deleted = 0", (email,)))


# ---------- 安全用户编码 ----------
def generate_user_code(db: DatabaseService) -> str:
	"""雪花简版：日期 + 6 位自增，唯一索引兜底"""
	date_str = datetime.now().strftime("%Y%m%d")
	prefix = f"U{date_str}"
	sql = "SELECT user_code FROM base_user WHERE user_code LIKE %s ORDER BY user_code DESC LIMIT 1"
	row = db.get_one(sql, (f"{prefix}%",))
	seq = int(row["user_code"][-6:]) + 1 if row else 1
	code = f"{prefix}{str(seq).zfill(6)}"
	# 唯一索引冲突重试
	while db.get_one("SELECT 1 FROM base_user WHERE user_code = %s", (code,)):
		seq += 1
		code = f"{prefix}{str(seq).zfill(6)}"
	return code


# ---------- 用户创建 ----------
async def create_user(db: DatabaseService, payload: UserCreate) -> int:
	if await username_exists(db, payload.username):
		raise BizException.from_error_code(ErrorCode.USERNAME_EXISTS)
	if await email_exists(db, payload.email):
		raise BizException.from_error_code(ErrorCode.EMAIL_EXISTS)
	data = payload.model_dump(exclude={"password"})
	data["hashed_password"] = hash_password(payload.password)
	data["user_code"] = generate_user_code(db)
	columns = list(data.keys())
	placeholders = ["%s"] * len(columns)
	sql = f"INSERT INTO base_user ({','.join(columns)}) VALUES ({','.join(placeholders)})"
	return db.insert(sql, tuple(data.values()))


# ---------- 用户列表（含角色） ----------
async def get_user_list(db: DatabaseService, request: UserListRequest) -> Tuple[int, List[UserWithRolesResponse]]:
	where, params = ["u.deleted = 0 and u.disabled = 0 "], []
	if request.username:
		where.append("u.username LIKE %s")
		params.append(f"%{request.username}%")
	if request.name:
		where.append("u.name LIKE %s")
		params.append(f"%{request.name}%")
	if request.role_id is not None:
		where.append("EXISTS (SELECT 1 FROM base_user_role ur WHERE ur.user_code = u.user_code AND ur.role_id = %s)")
		params.append(request.role_id)
	if request.disabled is not None:
		where.append("u.disabled = %s")
		params.append(request.disabled)
	where_clause = " AND ".join(where)

	total = db.get_one(f"SELECT COUNT(*) FROM base_user u WHERE {where_clause}", params)["COUNT(*)"] or 0
	offset = (request.page - 1) * request.size
	sql = f"""
        SELECT u.id, u.user_code, u.username, u.name, u.email, u.phone, u.disabled,
               u.created_at, u.updated_at, u.last_login
        FROM base_user u
        WHERE {where_clause}
        ORDER BY u.created_at DESC
        LIMIT %s OFFSET %s
    """
	params.extend([request.size, offset])
	rows = db.get_all(sql, params)

	users = []
	for row in rows:
		roles = [RoleResponse.model_validate(r) for r in await get_user_roles(db, row["user_code"])]
		users.append(UserWithRolesResponse.model_validate({**row, "roles": roles}))
	return total, users


# ---------- 用户角色 ----------
async def get_user_roles(db: DatabaseService, user_code: str) -> List[Role]:
	sql = """
          SELECT r.id,
                 r.role_code,
                 r.role_name,
                 r.description,
                 r.type,
                 r.enabled,
                 r.deleted,
                 r.version,
                 r.gmt_create,
                 r.gmt_update
          FROM base_role r
          LEFT JOIN base_user_role ur ON r.id = ur.role_id
          WHERE ur.user_code = %s
            AND r.deleted = 0
          ORDER BY r.type, r.role_name \
		  """
	rows = db.get_all(sql, (user_code,))
	return [Role.model_validate(r) for r in rows]


async def assign_roles_to_user(db: DatabaseService, user_code: str, role_ids: List[int]) -> bool:
	if not await get_user_by_code(db, user_code):
		raise BizException.from_error_code(ErrorCode.NOT_FOUND, message="用户不存在")
	db.update("DELETE FROM base_user_role WHERE user_code = %s", (user_code,))
	if role_ids:
		sql = "INSERT INTO base_user_role (user_code, role_id) VALUES (%s, %s)"
		for rid in role_ids:
			db.insert(sql, (user_code, rid))
	return True


async def remove_roles_from_user(db: DatabaseService, user_code: str, role_ids: Optional[List[int]] = None) -> bool:
	if not await get_user_by_code(db, user_code):
		raise BizException.from_error_code(ErrorCode.NOT_FOUND, message="用户不存在")
	if role_ids:
		placeholders = ",".join(["%s"] * len(role_ids))
		db.update(f"DELETE FROM base_user_role WHERE user_code = %s AND role_id IN ({placeholders})",
				  [user_code, *role_ids])
	else:
		db.update("DELETE FROM base_user_role WHERE user_code = %s", (user_code,))
	return True


# ---------- 用户状态 ----------
async def disable_user(db: DatabaseService, user_id: int) -> bool:
	return db.update("UPDATE base_user SET disabled = 1 WHERE id = %s AND deleted = 0", (user_id,)) > 0


async def enable_user(db: DatabaseService, user_id: int) -> bool:
	return db.update("UPDATE base_user SET disabled = 0 WHERE id = %s AND deleted = 0", (user_id,)) > 0


# ---------- 向后兼容 ----------
async def get_user_by_username(db: DatabaseService, username: str) -> Optional[UserInDB]:
	return await get_user_by_field(db, "username", username)
